package com.newcapec.dao.impl;

import com.newcapec.bean.NewsEntity;
import com.newcapec.dao.NewsDao;
import com.newcapec.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description:
 * @Author:yangshupeng
 * @Date: 2022/3/15 8:30
 * @Version:1.0
 */
public class NewsDaoImpl implements NewsDao {
    private Connection conn;
    private Statement stmt;
    private PreparedStatement pstmt;
    private ResultSet rs;
    @Override
    public boolean addNews(NewsEntity newsEntity) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "insert into news (title,descr,chnl_code,main_img,detail,keyword,source,top,state,optor,crtime,uptime)" +
                    "values (?,?,?,?,?,?,?,?,?,?,now(),now())";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setString(1,newsEntity.getTitle());
            pstmt.setString(2,newsEntity.getDescr());
            pstmt.setInt(3,newsEntity.getChnlcode());
            pstmt.setString(4,newsEntity.getMainImg());
            pstmt.setString(5,newsEntity.getDetail());
            pstmt.setString(6,newsEntity.getKeyword());
            pstmt.setString(7,newsEntity.getSource());
            pstmt.setInt(8,newsEntity.getTop());
            pstmt.setInt(9,newsEntity.getState());
            pstmt.setString(10,newsEntity.getOptor());


            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//添加成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }

    @Override
    public boolean deleteNews(String id) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "delete from news where id = ?";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setInt(1,Integer.parseInt(id));

            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//删除成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }

    @Override
    public boolean updateNews(NewsEntity newsEntity) {
        //设置一个标识符，用来判断是否添加成功
        boolean isFlag = false;
        try {
            //获取连接
            conn = JDBCUtil.getConn();
            //创建添加的sql语句
            String sql = "update news set title = ?,descr = ?,chnl_code = ?,main_img = ?,detail = ?,keyword = ?,source = ?,top = ?,state = ?,optor = ? ,uptime = now() where id = ?";
            //创建sql语句的对象
            pstmt = conn.prepareStatement(sql);

            //给占位符?设置值
            pstmt.setString(1,newsEntity.getTitle());
            pstmt.setString(2,newsEntity.getDescr());
            pstmt.setInt(3,newsEntity.getChnlcode());
            pstmt.setString(4,newsEntity.getMainImg());
            pstmt.setString(5,newsEntity.getDetail());
            pstmt.setString(6,newsEntity.getKeyword());
            pstmt.setString(7,newsEntity.getSource());
            pstmt.setInt(8,newsEntity.getTop());
            pstmt.setInt(9,newsEntity.getState());
            pstmt.setString(10,newsEntity.getOptor());
            pstmt.setInt(11,newsEntity.getId());

            //执行sql语句，并返回一个int类型的值（数据库中有几行数据被影响）
            int i = pstmt.executeUpdate();

            if (i != 0){
                isFlag = true;//修改成功
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接
            JDBCUtil.closeSource(conn,pstmt,null);
        }
        return isFlag;
    }

    @Override
    public List<NewsEntity> query(int offset,int pageSize,NewsEntity news) {
        List<NewsEntity> newsList = new ArrayList<>();
        conn = JDBCUtil.getConn();
        try {
            String sql = "select news.id,title,descr,chnl_code,name,main_img,detail,keyword,source,top,state,optor,news.crtime,news.uptime from news join news_chnl on news.CHNL_CODE = news_chnl.ID ";
            //select news.id,title,descr,name,main_img,detail,keyword,source,top,state,optor,news.crtime,news.uptime from news join news_chnl on news.CHNL_CODE = news_chnl.ID
            if (news != null && news.getTitle() != null){
                sql += "where title like ?";
            }
            sql += "limit ?,?";

            pstmt = conn.prepareStatement(sql);
            if (news != null && news.getTitle() != null){
                pstmt.setString(1,"%" + news.getTitle() + "%");
                pstmt.setInt(2,offset);
                pstmt.setInt(3,pageSize);
            }else {
                pstmt.setInt(1,offset);
                pstmt.setInt(2,pageSize);
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                NewsEntity newsEntity = new NewsEntity();
                newsEntity.setId(rs.getInt("id"));
                newsEntity.setTitle(rs.getString("title"));
                newsEntity.setDescr(rs.getString("name"));
                newsEntity.setChnlcode(rs.getInt("chnl_code"));
                newsEntity.setMainImg(rs.getString("main_img"));
                newsEntity.setDetail(rs.getString("detail"));
                newsEntity.setKeyword(rs.getString("keyword"));
                newsEntity.setSource(rs.getString("source"));
                newsEntity.setTop(rs.getInt("top"));
                newsEntity.setState(rs.getInt("state"));
                newsEntity.setOptor(rs.getString("optor"));
                newsEntity.setCrtime(rs.getString("crtime"));
                newsEntity.setUptime(rs.getString("uptime"));
                newsList.add(newsEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,pstmt,rs);
        }
        return newsList;
    }

    @Override
    public NewsEntity queryById(String id) {
        NewsEntity newsEntity = null;
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,title,descr,chnl_code,main_img,detail,keyword,source,top,state,optor,crtime,uptime from news where id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(id));
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()){
                newsEntity = new NewsEntity();
                newsEntity.setId(rs.getInt("id"));
                newsEntity.setTitle(rs.getString("title"));
                newsEntity.setDescr(rs.getString("descr"));
                newsEntity.setChnlcode(rs.getInt("chnl_code"));
                newsEntity.setMainImg(rs.getString("main_img"));
                newsEntity.setDetail(rs.getString("detail"));
                newsEntity.setKeyword(rs.getString("keyword"));
                newsEntity.setSource(rs.getString("source"));
                newsEntity.setTop(rs.getInt("top"));
                newsEntity.setState(rs.getInt("state"));
                newsEntity.setOptor(rs.getString("optor"));
                newsEntity.setCrtime(rs.getString("crtime"));
                newsEntity.setUptime(rs.getString("uptime"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,stmt,rs);
        }
        return newsEntity;
    }

    @Override
    public List<NewsEntity> queryByColumnId(int columnId) {
        List<NewsEntity> newsList = new ArrayList<>();
        conn = JDBCUtil.getConn();
        try {
            String sql = "select id,title,descr,chnl_code,main_img,detail,keyword,source,top,state,optor,crtime,uptime from news where chnl_code = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,columnId);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()){
                NewsEntity newsEntity = new NewsEntity();
                newsEntity.setId(rs.getInt("id"));
                newsEntity.setTitle(rs.getString("title"));
                newsEntity.setDescr(rs.getString("descr"));
                newsEntity.setChnlcode(rs.getInt("chnl_code"));
                newsEntity.setMainImg(rs.getString("main_img"));
                newsEntity.setDetail(rs.getString("detail"));
                newsEntity.setKeyword(rs.getString("keyword"));
                newsEntity.setSource(rs.getString("source"));
                newsEntity.setTop(rs.getInt("top"));
                newsEntity.setState(rs.getInt("state"));
                newsEntity.setOptor(rs.getString("optor"));
                newsEntity.setCrtime(rs.getString("crtime"));
                newsEntity.setUptime(rs.getString("uptime"));
                newsList.add(newsEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,stmt,rs);
        }
        return newsList;
    }

    @Override
    public int getAllCount(String title) {
        conn = JDBCUtil.getConn();
        try {
            String sql = "select count(1) from news ";
            if (title != null){
                sql += "where title like ?";
            }


            pstmt = conn.prepareStatement(sql);
            if (title != null){
                pstmt.setString(1,"%" + title + "%");
            }
            rs = pstmt.executeQuery();
            if (rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.closeSource(conn,pstmt,rs);
        }
        return 0;
    }
}
